ajMatch function
Available since AlchemyJ v4.3.1
Description
The ajMatch function returns the position of an item in an array.
Syntax
ajMatch(lookup_value, lookup_array, [match_type], [cache],[return_first_match])
Argument Name | Argument Type | Description |
---|---|---|
lookup_value (required) | Variant | The value to match in lookup_array. |
lookup_array (required) | Variant | A range of cells or an array reference. |
match_type (optional) | Integer | 1 = exact or next smallest. 0 = exact match and always return the first position when multiple value are matched. -1 = exact or next largest. The default value is 1. The array has to be sorted when match type is 1 or -1. |
cache (optional) | Boolean | TRUE means to cache the lookup_array range. Subsequent ajMatch call to the same range will get the lookup_array from cache instead. With the cache enable, the performance will be much better than Excel Match() when the same range is used for matches many times in a function point. FALSE means will not use cache. The default value is False. |
return_first_match (optional) | Boolean | TRUE means to return the position of the match with the first position in sequence. FALSE means to return the position of the first found match. The default value is FALSE. This option only works when match type is not 0. |
The function will return:
1) Return Value: Position of the lookup value
2) Return Type: Number
Example
The ajMatch is using dichotomy to search the array when the match type is not 0, the values in lookup_array need to be sorted.
Example 1 - Match Type is 1
In this sample, we lookup "HKEX" in the array from B2 to B9, the values of lookup_array are sorted in ascending order. Match_type is omitted, it is using the default value 1, thus it will return 4 as the first found record as the result.
When the return_first_match is TRUE. Then it will search the array by sequence, and the first match position is 3.
Example 2- Match Type is 0
The match_type is 0, ajMatch exactly matches value and always returns the first position when multiple value are matched.
If the cache is TRUE, the values in lookup_array need to be sorted since it will use dichotomy to search the array.
Example 3- Match Type is -1
In this sample, we lookup 12 in the array from B2 to B9, the values of lookup_array are sorted in descending order.
If the lookup value is not matched, the position of the smallest value greater than the lookup value is returned.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type.
Error Scenario |
---|
The range of lookup_array with multiple rows and columns. |
Lookup value is empty. |
Lookup array is empty. |
Match type is not 1,0,-1. |